package com.jh.user.repository;

import com.jh.user.pojo.ChannelRate;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public interface ChannelRateRepository extends JpaRepository<ChannelRate,String>,JpaSpecificationExecutor<ChannelRate>{


	@Query("select channelRate  from ChannelRate channelRate  where channelRate.status = '1' and channelRate.userId=:user_id and channelRate.brandId=:brand_id and channelRate.channelId=:channel_id and channelRate.payType=:payType")
	ChannelRate findChannelRateByUserid(@Param("user_id") long userid, @Param("brand_id") long brandid, @Param("channel_id") long channelid,@Param("payType") short payType);


	@Query("select channelRate  from ChannelRate channelRate  where  channelRate.brandId=:brand_id and channelRate.channelId=:channel_id")
	List<ChannelRate>  findChannelRateByBrandid(@Param("brand_id") long brandid, @Param("channel_id") long channelid);

	@Modifying
	@Query("delete from ChannelRate channelRate  where channelRate.userId=:user_id and channelRate.brandId=:brand_id and channelRate.channelId=:channel_id")
	void delChannelRateByUserid(@Param("user_id") long userid, @Param("brand_id") long brandid, @Param("channel_id") long channelid);

	@Modifying
	@Query("update ChannelRate channelRate set rate=:minrate where channelRate.brandId=:brandid  and channelRate.channelId=:channelid and channelRate.userId=:userid")
	void updChannelRateBybrandidAndChannelidanduserId(@Param("minrate") BigDecimal minrate,@Param("brandid") long brandid, @Param("channelid") long channelid, @Param("userid") long userid);

	//根据userid注销用户该服务记录
	@Modifying
	@Query("delete from ChannelRate channelRate where channelRate.userId=:userid")
	void delChannelRateByUserid(@Param("userid") long userid);

	@Query("select channelRate from ChannelRate channelRate where channelRate.userId = :userId")
    List<ChannelRate> findChannelRateListByUserid(@Param("userId") long userId);

	@Query("update ChannelRate channelRate set channelRate.gradeRate = :gradeRate where channelRate.payType = :payType and channelRate.channelId = :channelId")
	void updateChannelByChannelIdAndPayType(@Param("channelId") long channelId, @Param("payType") Short payType, @Param("gradeRate") BigDecimal gradeRate);

	@Query("select channelRate from ChannelRate channelRate where channelRate.userId=:userId and channelRate.brandId=:brandId and channelRate.channelId in :channelIds")
	List<ChannelRate> findChannelRate(@Param("userId") Long userId, @Param("brandId") Long brandId, @Param("channelIds") List<Long> channelIds);

	/**
	 * 查询指定用户的通道费率信息
	 *
	 * @param userId 用户id
	 * @return 返回用户的通道费率信息
	 */
	List<ChannelRate> findByUserId(Long userId);

	@Query("select cr from ChannelRate cr where cr.userId=:userId and cr.brandId=:brandId and cr.channelId=:channelId and cr.payType=:payType")
	ChannelRate findChannelRate(@Param("userId") long userId, @Param("brandId") long brandId, @Param("channelId") long channelId, @Param("payType") short payType);

	@Query("select cr from ChannelRate cr where cr.userId=:userId and cr.brandId=:brandId and cr.channelId=:channelId and cr.payType=:payType and cr.channelMerType=:channelMerType")
	ChannelRate findChannelRate(@Param("userId") long userId, @Param("brandId") long brandId, @Param("channelId") long channelId, @Param("payType") short payType, @Param("channelMerType") String channelMerType);

	@Query("select cr from ChannelRate cr where cr.userId=:userId  and cr.status=:status")
	List<ChannelRate> findChannelRateByUseridAndGrade(@Param("userId") long userId,  @Param("status") String status);

	@Query("select cr from ChannelRate cr where cr.userId = ?1  and cr.status = ?2 and cr.channelId=?3 and cr.payType = ?4")
	ChannelRate findChannelRate(long userId,String status,long channelId,short payType);

	@Query("select c from ChannelRate c where c.userId = :userId and c.brandId = :brandId")
    List<ChannelRate> queryByUserId(@Param("userId") long userId, @Param("brandId") long brandId);

//	@Query("update ChannelRate channelRate set channelRate.gradeRate = :gradeRate ,channelRate.rate = :rate,channelRate.extraFee = :extraFee,channelRate.brandRate = :brandRate where channelRate.payType = :payType and channelRate.channelId = :channelId and channelRate.channelMerType = :channelMerType")
//	void updateChannelRate(@Param("gradeRate") BigDecimal gradeRate,@Param("rate") BigDecimal rate,@Param("extraFee") BigDecimal extraFee,@Param("brandRate") BigDecimal brandRate,@Param("channelId") long channelId, @Param("payType") Short payType, @Param("channelMerType") String channelMerType);

//	INSERT INTO `user`.`t_channel_rate` (`id`, `user_id`, `brand_id`, `channel_id`, `rate`, `extra_fee`, `grade_rate`, `status`, `pay_type`, `update_time`, `create_time`, `brand_rate`, `channel_mer_type`) VALUES ('288', '159221308', '407', '1', '0.0060', '2', '0.0058', '1', '1', '2018-12-18 19:03:03', '2018-12-18 19:03:03', '0.0046', '2');
//	@Query("insert into t_channelRate (user_id, brand_id, channel_id, rate, extra_fee, grade_rate, status, pay_type, update_time, create_time, brand_rate, channel_mer_type) VALUES " +
//			"()")
//	void insertChannelRate();
}
